import fnmatch
import glob
import os
import posixpath
import shutil
import sqlite3
import stat
from datetime import datetime

import paramiko
from sqlalchemy.ext.asyncio import AsyncSession
from starlette.requests import Request

from config import env
from config.constant import CommonConstant
from exceptions.exception import ServiceException
from module_admin.entity.vo.common_vo import CrudResponseModel
from module_kylin.dao.trade_dao import TradeDao
from module_kylin.entity.vo.trade_vo import TradeModel, TradePageQueryModel, DeleteTradeModel
from utils.common_util import CamelCaseUtil
from utils.log_util import logger


class TradeService:
    """
    交易流水管理模块服务层
    """

    @classmethod
    async def get_trade_list_services(
            cls, query_db: AsyncSession, query_object: TradePageQueryModel, is_page: bool = False):
        """
        获取部门列表信息service

        :param query_db: orm对象
        :param query_object: 查询参数对象
        :param is_page: 是否开启分页
        :return: 参数配置列表信息对象
        """
        trade_list_result = await TradeDao.get_trade_list(query_db, query_object, is_page)

        return trade_list_result

    @classmethod
    async def trade_detail_services(cls, query_db: AsyncSession, id: int):
        """
        获取成交记录详细信息service

        :param query_db: orm对象
        :param id: 成交记录主键id
        :return: 成交记录id对应的信息
        """
        trade = await TradeDao.get_trade_detail_by_id(query_db, id=id)
        if trade:
            result = TradeModel(**CamelCaseUtil.transform_result(trade))
        else:
            result = TradeModel(**dict())

        return result

    @classmethod
    async def check_trade_unique_services(cls, query_db: AsyncSession, page_object: TradeModel):
        """
        校验参数键名是否唯一service

        :param query_db: orm对象
        :param page_object: 参数配置对象
        :return: 校验结果
        """
        id = -1 if page_object.id is None else page_object.id
        trade = await TradeDao.get_trade_detail_by_info(
            query_db, TradeModel(name=page_object.name, tradeId=page_object.trade_id))
        if trade:
            if trade and trade.id != id:
                return CommonConstant.NOT_UNIQUE
        return CommonConstant.UNIQUE

    @classmethod
    async def add_trade_services(cls, query_db: AsyncSession, page_object: TradeModel):
        """
        新增参数配置信息service

        :param query_db: orm对象
        :param page_object: 新增参数配置对象
        :return: 新增参数配置校验结果
        """
        if not await cls.check_trade_unique_services(query_db, page_object):
            raise ServiceException(
                message=f'新增策略{page_object.name},编号{page_object.trade_id}失败，策略成交记录已存在')
        else:
            try:
                await TradeDao.add_trade(query_db, page_object)
                await query_db.commit()
                return CrudResponseModel(is_success=True, message='新增成功')
            except Exception as e:
                await query_db.rollback()
                raise e

    @classmethod
    async def edit_config_services(cls, query_db: AsyncSession, page_object: TradeModel):
        """
        编辑参数配置信息service

        :param query_db: orm对象
        :param page_object: 编辑参数配置对象
        :return: 编辑参数配置校验结果
        """
        edit_trade = page_object.model_dump(exclude_unset=True)
        trade_info = await cls.trade_detail_services(query_db, page_object.id)
        if trade_info.id:
            if not await cls.check_trade_unique_services(query_db, page_object):
                raise ServiceException(
                    message=f'修改策略{page_object.name},编号{page_object.trade_id}失败，策略成交记录已存在')
            else:
                try:
                    await TradeDao.edit_trade(query_db, edit_trade)
                    await query_db.commit()
                    return CrudResponseModel(is_success=True, message='更新成功')
                except Exception as e:
                    await query_db.rollback()
                    raise e
        else:
            raise ServiceException(message='策略成交记录不存在')

    @classmethod
    async def delete_trade_services(cls, request: Request, query_db: AsyncSession, page_object: DeleteTradeModel):
        """
        删除参数配置信息service

        :param request: Request对象
        :param query_db: orm对象
        :param page_object: 删除参数配置对象
        :return: 删除参数配置校验结果
        """
        if page_object.trade_ids:
            trade_id_list = page_object.trade_ids.split(',')
            try:
                for trade_id in trade_id_list:
                    await TradeDao.delete_trade(query_db, TradeModel(id=int(trade_id)))
                await query_db.commit()
                return CrudResponseModel(is_success=True, message='删除成功')
            except Exception as e:
                await query_db.rollback()
                raise e
        else:
            raise ServiceException(message='传入参数配置id为空')

    @staticmethod
    async def download_kylin_trade_service(server_id: str):
        """下载交易流水文件service"""
        # 定义内置排除规则
        exclude_patterns: list[str] = [".*", "clean_old_logs.py", "strategies", "tk.csv", "log.txt", "*.log"]
        # hy_server
        exclude_patterns.extend(["hy_manual", "hy_server"])
        # hs_server
        exclude_patterns.extend(["get_data_from_akshare.py", "hs_manual", "hs_server"])

        try:
            # 构建完整本地路径
            local_path = os.path.join(env.KylinConfig.get('KYLIN_FILE_PATH', ''), server_id)
            if os.path.exists(local_path):
                shutil.rmtree(local_path)
                logger.info(f"已删除目录: {local_path}")
            os.makedirs(os.path.dirname(local_path), exist_ok=True)

            # 获取指定服务器的配置
            server_config = env.KylinConfig.get('SFTP_SERVERS', {}).get(server_id)
            if not server_config:
                raise ValueError(f"未找到服务器配置: {server_id}")

            # 创建SSH连接
            transport = paramiko.Transport((server_config['host'], server_config['port']))
            transport.connect(username=server_config['user'], password=server_config['password'])

            # 从配置获取远程路径
            remote_path = server_config['remote_path']

            # 创建SFTP客户端
            sftp = paramiko.SFTPClient.from_transport(transport)

            def recursive_download(sftp_client, remote_dir, local_dir):
                """递归下载远程目录到本地，保持目录结构"""
                # 确保本地目录存在
                os.makedirs(local_dir, exist_ok=True)

                # 列出远程目录中的所有项目
                for entry in sftp_client.listdir_attr(remote_dir):
                    remote_full_path = posixpath.join(remote_dir, entry.filename)
                    local_full_path = os.path.join(local_dir, entry.filename)

                    # 排除逻辑
                    # 确保路径分割在所有平台上一致工作
                    path_parts = remote_full_path.split(posixpath.sep)
                    # 移除空字符串（处理以/开头的路径）
                    path_parts = [part for part in path_parts if part]
                    
                    should_skip = any([
                        entry.filename in exclude_patterns,
                        any(fnmatch.fnmatch(entry.filename, p) for p in exclude_patterns),
                        any(p in path_parts for p in exclude_patterns),
                        # 增加直接路径包含检查，确保在所有平台上都能正确匹配
                        any(p in remote_full_path for p in exclude_patterns)
                    ])

                    if should_skip:
                        logger.info(f"[{server_id}] 已跳过排除项: {remote_full_path}")
                        continue

                    # 如果是目录，则递归下载
                    if stat.S_ISDIR(entry.st_mode):
                        recursive_download(sftp_client, remote_full_path, local_full_path)
                    # 如果是文件，则直接下载
                    else:
                        sftp_client.get(remotepath=remote_full_path, localpath=local_full_path)
                        logger.info(f"[{server_id}] 下载成功: {remote_full_path} -> {local_full_path}")

            # 开始递归下载
            recursive_download(sftp, remote_path, local_path)
            # 关闭连接
            sftp.close()
            transport.close()
            logger.info(f"[{server_id}] 递归下载完成")
            return True, "文件批量下载完成"
        except Exception as e:
            logger.error(f"[{server_id}] 下载失败: {str(e)}")
            return False, str(e)

    @staticmethod
    async def load_kylin_trade_service(query_db: AsyncSession, server_id: str):
        """交易文件加载service"""
        try:
            # 构建完整本地路径
            local_path = os.path.join(env.KylinConfig.get('KYLIN_FILE_PATH', ''), server_id)
            os.makedirs(os.path.dirname(local_path), exist_ok=True)

            # 新增数据库文件处理逻辑
            processed_files = 0
            success_count = 0

            # 遍历查找所有.db文件
            for db_file in glob.glob(os.path.join(local_path, "**", "*.db"), recursive=True):
                try:
                    # 连接SQLite数据库
                    with sqlite3.connect(db_file) as conn:
                        cursor = conn.cursor()
                        cursor.execute("SELECT * FROM trade")
                        trades = cursor.fetchall()
                        # 批量插入数据库
                        for trade in trades:
                            trade_model = TradeModel(
                                tradeId=trade[0],
                                name=trade[1],
                                date=datetime.strptime(str(trade[2]), "%Y%m%d").date(),
                                time=datetime.strptime(f"{trade[2]} {trade[3]}", "%Y%m%d %H:%M:%S").time(),
                                price=trade[4],
                                direction=trade[5],
                                offset=trade[6],
                                volume=trade[7],
                                pos=trade[8]
                            )
                            await TradeDao.add_trade(query_db, trade_model)
                            success_count += 1
                        processed_files += 1
                        logger.info(f"成功处理文件: {db_file}，共{len(trades)}条记录")
                except Exception as e:
                    logger.error(f"处理文件{db_file}时出错: {str(e)}")
                    continue
            await query_db.commit()
            return True, f"成功处理{processed_files}个文件，插入{success_count}条记录"
        except Exception as e:
            await query_db.rollback()
            logger.error(f"[{server_id}] 数据加载失败: {str(e)}")
            return False, str(e)
